package com.fireghost.jdt.database;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class JournalDeTravailDbAdapter {
	
	///////    Attributes     ///////
	/***  Table constants  ***/
	private static final String[][][] TABLES_CONFIGURATIONS = {
		{
			{"types"},
			{"_id", 								"sDescription"},
			{"integer primary key autoincrement", 	"text"}
		},{
			{"jobs"},
			{"_id", 								"sDescription", "iStartTimestmap", 	"iEndTimestamp", 	"idx_type"},
			{"integer primary key autoincrement", 	"text", 		"integer", 			"integer", 			"integer"}
		}
	};
	private static final String DATABASE_NAME 		= "JournalDeTravail";
	private static final int 	DATABASE_VERSION 	= 1;
	
	
	/***  Database attributes  ***/
	private JournalDeTravailDbHelper oDbHelper;
	private SQLiteDatabase oDb;
	
	
	
	///////    Classes     ///////
	private static class JournalDeTravailDbHelper extends SQLiteOpenHelper {

		public JournalDeTravailDbHelper(Context p_oContext) {
			super(p_oContext, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// Will contains the request for database's creation
			String sCreateTable = "";
			
			// Create the request
			for (String[][] aTableConfiguration : TABLES_CONFIGURATIONS) {
				
				sCreateTable += "CREATE TABLE " + aTableConfiguration[0][0] + " (";
				for (int i = 0; i < aTableConfiguration[1].length; i++) {
					sCreateTable += aTableConfiguration[1][i] + " " + aTableConfiguration[2][i] + ", ";
				}
				sCreateTable = sCreateTable.substring(0, sCreateTable.length() - 2);
				sCreateTable += ");";
				
			}
			
			// Create the tables
			db.execSQL(sCreateTable);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// Delete the olds tables and create the news tables
			for (String[][] aTableConfiguration : TABLES_CONFIGURATIONS) {
				db.execSQL("DROP TABLE IF EXISTS " + aTableConfiguration[0][0]);
			}
			onCreate(db);
		}
	}
	
	
	
	///////    Methods     ///////
	/*** Construct ***/
	public JournalDeTravailDbAdapter(Context p_oContext) {
		oDbHelper = new JournalDeTravailDbHelper(p_oContext);
	}
	
	
	/*** Open - close ***/
	public void open() {
		oDb = oDbHelper.getWritableDatabase();
	}
	
	public void close() {
		oDb.close();
	}
	
	
	/*** getters ***/
	public ArrayList<JobDbEntry> getDailyJobs(long p_iTimestampDayAtMidnight) {
		
		// Get the timestamp for one day later
		long iTimestampOneDayAfter = this.addOneDayToTimestamp(p_iTimestampDayAtMidnight);
		
		// Query for getting the daily jobs
		Cursor oCursorDailyJobs = 
				oDb.query(
						TABLES_CONFIGURATIONS[1][0][0],
						TABLES_CONFIGURATIONS[1][1],
						TABLES_CONFIGURATIONS[1][1][2] +" >= "+ p_iTimestampDayAtMidnight +" AND "+ TABLES_CONFIGURATIONS[1][1][2] +" < "+ iTimestampOneDayAfter,
						null, null, null, null);
		
		// Put all jobs in an array list
		ArrayList<JobDbEntry> a_oJobs = new ArrayList<JobDbEntry>();
		while(oCursorDailyJobs.moveToNext()) {
			a_oJobs.add(cursorToJob(oCursorDailyJobs));
		}
		oCursorDailyJobs.close();
		
		return a_oJobs;
	}
	
	public JobDbEntry getJobByID(long p_iJobID) {
		// Query for getting all fields from a job id given
		Cursor oCursorJob =
				oDb.query(
						TABLES_CONFIGURATIONS[1][0][0],
						TABLES_CONFIGURATIONS[1][1],
						TABLES_CONFIGURATIONS[1][1][0]+"="+p_iJobID,
						null, null, null, null);
		
		// Put the job in a Job object
		oCursorJob.moveToFirst();
		JobDbEntry oJob = this.cursorToJob(oCursorJob);
		oCursorJob.close();
		
		return oJob;
	}
	
	public ArrayList<TypeDbEntry> getTypes() {
		// Query for getting the types
		Cursor oCursorTypes = 
				oDb.query(TABLES_CONFIGURATIONS[0][0][0],
					TABLES_CONFIGURATIONS[0][1],
					null, null, null, null, null);
		
		// Put the types in the array list
		ArrayList<TypeDbEntry> a_oTypes = new ArrayList<TypeDbEntry>();
		while (oCursorTypes.moveToNext()) {
			a_oTypes.add(cursorToType(oCursorTypes));
		}
		oCursorTypes.close();
		
		return a_oTypes;
	}
	
	public TypeDbEntry getTypeById(long p_iTypeId) {
		// Query for getting the type
		Cursor oCursorTypeContent =
				oDb.query(TABLES_CONFIGURATIONS[0][0][0],
						TABLES_CONFIGURATIONS[0][1],
						p_iTypeId+"="+TABLES_CONFIGURATIONS[0][1][0],
						null, null, null, null);
		
		TypeDbEntry oType;
		if (oCursorTypeContent.getCount() <= 0) {
			oType = new TypeDbEntry("");
		}
		else {
			oCursorTypeContent.moveToFirst();
			oType = cursorToType(oCursorTypeContent);
			oCursorTypeContent.close();
		}
		
		return oType;
	}
	
	public long getTypeIdByTypeDescription(String p_sTypeDescription) {
		Cursor oCursorType =
				oDb.query(TABLES_CONFIGURATIONS[0][0][0],
						TABLES_CONFIGURATIONS[0][1],
						TABLES_CONFIGURATIONS[0][1][1] +"='"+ p_sTypeDescription +"'",
						null, null, null, null);
		
		if (oCursorType.getCount() <= 0) {
			return -1;
		}
		else {
			oCursorType.moveToFirst();
			return oCursorType.getLong(0);
		}
	}
	
	
	/***  Setters  ***/
	public long insertNewJob(JobDbEntry p_oJob) {
		ContentValues oNewJobValues = new ContentValues();
		
		oNewJobValues.put(TABLES_CONFIGURATIONS[1][1][1], p_oJob.getJobDescription());
		oNewJobValues.put(TABLES_CONFIGURATIONS[1][1][2], p_oJob.getTimestampStart());
		oNewJobValues.put(TABLES_CONFIGURATIONS[1][1][3], p_oJob.getTimestampEnd());
		oNewJobValues.put(TABLES_CONFIGURATIONS[1][1][4], p_oJob.getTypeID());
		
		return oDb.insert(TABLES_CONFIGURATIONS[1][0][0], null, oNewJobValues);
	}
	
	public void updateJob(JobDbEntry p_oJob) {
		ContentValues oJobValues = new ContentValues();
		
		oJobValues.put(TABLES_CONFIGURATIONS[1][1][1], p_oJob.getJobDescription());
		oJobValues.put(TABLES_CONFIGURATIONS[1][1][2], p_oJob.getTimestampStart());
		oJobValues.put(TABLES_CONFIGURATIONS[1][1][3], p_oJob.getTimestampEnd());
		oJobValues.put(TABLES_CONFIGURATIONS[1][1][4], p_oJob.getTypeID());
		
		oDb.update(TABLES_CONFIGURATIONS[1][0][0], oJobValues, TABLES_CONFIGURATIONS[1][1][0] +"="+ p_oJob.getJobID(), null);
	}
	
	public void deleteJob(JobDbEntry p_oJobDbEntry) {
		oDb.delete(TABLES_CONFIGURATIONS[1][0][0], TABLES_CONFIGURATIONS[1][1][0] +"="+ p_oJobDbEntry.getJobID(), null);
	}
	
	public long insertNewType(TypeDbEntry p_oType) {
		long iTypeId = this.getTypeIdByTypeDescription(p_oType.getTypeDescription());
		
		if (iTypeId == -1) {
			ContentValues oNewTypeValues = new ContentValues();
			
			oNewTypeValues.put(TABLES_CONFIGURATIONS[0][1][1], p_oType.getTypeDescription());
			
			return oDb.insert(TABLES_CONFIGURATIONS[0][0][0], null, oNewTypeValues);
		}
		else {
			return iTypeId;
		}
		
		
	}
	
	public void emptyDatabase() {
		for(String[][] aDatabaseName : TABLES_CONFIGURATIONS) {
			oDb.delete(aDatabaseName[0][0], null, null);
		}
	}
	
	
	/***  Converter  ***/
	public JobDbEntry cursorToJob(Cursor p_oCursorJob) {
		JobDbEntry oJob = new JobDbEntry(
				p_oCursorJob.getLong(0),
				p_oCursorJob.getString(1),
				p_oCursorJob.getLong(2),
				p_oCursorJob.getLong(3),
				p_oCursorJob.getLong(4));
		
		return oJob;
	}
	
	public TypeDbEntry cursorToType(Cursor p_oCursorType) {
		TypeDbEntry oType = new TypeDbEntry(
				p_oCursorType.getLong(0),
				p_oCursorType.getString(1));
		
		return oType;
	}
	
	private long addOneDayToTimestamp(long p_iTimestampStart) {
		GregorianCalendar oCal = new GregorianCalendar();
		oCal.setTimeInMillis(p_iTimestampStart);
		oCal.set(Calendar.DAY_OF_MONTH, oCal.get(Calendar.DAY_OF_MONTH) + 1);
		return oCal.getTimeInMillis();
	}
}
